package poi;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;



import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelReader {
    private POIFSFileSystem fs;
    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private HSSFRow row;
    private final static int STRING_LENGTH=20;


    /**
     * 读取Excel表格表头内容
     * @return String 表头内容的数组
     */

    public String[] readExcelTitle(InputStream is){
        try {
            fs=new POIFSFileSystem(is);
            wb=new HSSFWorkbook(fs);
        }catch (IOException e){
            e.printStackTrace();
        }
        sheet=wb.getSheetAt(0);
        row=sheet.getRow(0);
        //标题总列数
        int colNum=row.getPhysicalNumberOfCells();
        String[] title=new String[colNum+1];
        String sheeName=wb.getSheetName(0);
        title[0]="工作表："+sheeName+"\n";
        //colNum 算得是最小单元格数目 为7  但实质上只需要1即可存储
        for (int i = 1; i <=colNum; i++) {
            title[i]=getCellFormatValue(row.getCell(i));
        }

        return title;
    }

    /**
     * 读取Excel数据内容
     * @return Map 包含单元格数据内容的Map对象
     */

    public  Map<Integer,String> readExcelContent(InputStream is) {
        Map<Integer, String> content = new HashMap<>();
        String str = "";

        try {
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        //读取总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        //第一行为表头，第二行为正文内容
        for (int i = 1; i <=rowNum; i++) {
            row = sheet.getRow(i);

            int j = 1;
            while (j < colNum) {
                //每个单元格的数据用-分割  便于使用String类的replace（）方法进行分割
                // 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean
                //str += getStringCellValue(row.getCell((short) j)).trim() + "-";
                str += getCellFormatValue(row.getCell(j)).trim() + "    ";
                j++;
            }
            content.put(i, str);
            str = "";
        }
        return content;
    }

    /**
     * 获取单元格数据内容为字符串类型的数据
     *
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */

    private String getStringCellValue(HSSFCell cell){
        String strCell="";
        switch (cell.getCellTypeEnum()) {
            case STRING:
                strCell = cell.getStringCellValue();
                break;
            case NUMERIC:
                strCell = String.valueOf(cell.getNumericCellValue());
                break;
            case BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK:
                strCell = "";
                break;
            default:
                strCell = "";
                break;
        }
        if (strCell.equals("")||strCell==null) {
            return "";
        }
        if (cell==null) {
            return "";
        }
        return strCell;

        }

    /**
     * 获取单元格数据内容为日期类型的数据
     *
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */

    private String getDateCellValue(HSSFCell cell){
            String result="";
            try {
                CellType cellType=cell.getCellTypeEnum();
                if (cellType== CellType.NUMERIC){
                    GregorianCalendar calendar=new GregorianCalendar();
                    Date date=cell.getDateCellValue();
                    calendar.setTime(date);

                    result=(calendar.get(Calendar.YEAR)+1900)+"-"+(calendar.get(Calendar.MONTH)+1)+"-"+calendar.get(Calendar.DATE);
                }else if (cellType==CellType.STRING){
                    String date=getStringCellValue(cell);
                    result = date.replaceAll("[年月]", "-").replace("日", "").trim();
                } else if (cellType == CellType.BLANK) {
                    result = "";
            }
        } catch (Exception e) {
        System.out.println("日期格式不正确!");
        e.printStackTrace();
    }
    return result;
    }

    /**
     * 根据HSSFCell类型设置数据
     * @param cell
     * @return
     */

    private String getCellFormatValue(HSSFCell cell){
            String cellvalue="";

        if (cell!=null){
            switch (cell.getCellTypeEnum()) {
                case NUMERIC:
                case FORMULA: {
                    // 判断当前的cell是否为Date
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    //  如果是Date类型则，转化为Data格式
                    //  方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                    //  cellvalue = cell.getDateCellValue().toLocaleString();

                    //  方法2：这样子的data格式是不带带时分秒的：2011-10-12
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        cellvalue =sdf.format(date)+addSpace(cellvalue.length());
                    }
                    //  如果是纯数字
                    else {
                    //  取得当前Cell的数值
                        NumberFormat numberFormat=NumberFormat.getNumberInstance();
                        numberFormat.setMaximumFractionDigits(2);
                        cellvalue=numberFormat.format(cell.getNumericCellValue());
                        cellvalue +=addSpace(cellvalue.length());
                    }
                    break;
                }
                    // 如果当前Cell的Type为STRING
                case STRING:
                    // 取得当前的Cell字符串
                    //cellvalue = cell.getRichStringCellValue().getString();
                    boolean isNum=stringIsNumberic(cell.getStringCellValue());
                    if (isNum){
                        Double d=Double.parseDouble(cell.getStringCellValue());
                        DecimalFormat decimalFormat=(DecimalFormat)NumberFormat.getNumberInstance();
                        decimalFormat.setMinimumFractionDigits(3);
                        cellvalue=decimalFormat.format(d);
                        cellvalue += addSpace(cellvalue.length());
                    }else {
                        cellvalue = cell.getStringCellValue();
                        cellvalue += addSpace(cellvalue.length());
                    }


                    break;
                    // 默认的Cell值
                default:
                    cellvalue = " ";
            }
        } else {
            //如果cell为null
            cellvalue = "";
        }
        return cellvalue;

    }

    public String addSpace(int length){
        String str="";
        if (length<STRING_LENGTH){
            for (int i = 1; i <=STRING_LENGTH-length; i++) {
                str +="-";
            }
        }

        return str;
    }

    public boolean stringIsNumberic(String str){
        String patternString="^-?[0-9]*\\.[0-9]*";
        Pattern pattern=Pattern.compile(patternString);
        Matcher isNum=pattern.matcher(str);
        if (!isNum.matches()){
            return false;
        }else {
            return true;
        }
    }

    public static void main(String[] args) {
        try {
            InputStream is=new FileInputStream("testfile.xls");
            ExcelReader excelReader=new ExcelReader();
            String[] title=excelReader.readExcelTitle(is);
            System.out.print("Excel标题是：");
            for (String s :title) {
                System.out.print(s+" ");
            }

            InputStream is2=new FileInputStream("testfile.xls");
            Map<Integer,String> map=excelReader.readExcelContent(is2);
            System.out.println();
            for (int i = 1; i <=map.size(); i++) {
                System.out.println(map.get(i));
            }

        }catch (FileNotFoundException e){
            System.out.println("未找到指定的文件！");
            e.printStackTrace();
        }
    }

}



